package hiram.util;

import com.fr.base.Utils;
import com.fr.data.core.db.DBUtils;
import com.fr.data.impl.JDBCDatabaseConnection;
import com.fr.general.GeneralContext;
import hiram.util.bean.User;
import hiram.util.dao.EmbDataSource;
import com.fr.record.JdbcTemplate;
import com.fr.stable.EnvChangedListener;

import java.io.UnsupportedEncodingException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import hiram.util.dao.UserDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

public class MyDbUtils {
	private static JDBCDatabaseConnection db = null;

	static {
		GeneralContext.addEnvChangedListener(new EnvChangedListener() {
			public void envChanged() {
				db = null;
			}
		});
	}

	public static JDBCDatabaseConnection getDB() {
		if (db == null) {
			refreshDB();
		}
		return db;
	}

	public static void executeSqlByFile(String file) throws Exception {
		String sql = "";

		try {
			sql = Utils.inputStream2String(MyDbUtils.class.getResourceAsStream(file), "GBK");
		} catch (UnsupportedEncodingException e) {
			throw e;
		}

		new JdbcTemplate(getDB()).execute(sql);
	}
	public static void executeSql(String sql) throws Exception{
		new JdbcTemplate(getDB()).execute(sql);
	}

	public static void dropTable(String table) throws Exception {
		new JdbcTemplate(getDB()).execute("drop table " + table + ";");
	}

	public static void clearTable(String table) throws Exception {
		new JdbcTemplate(getDB()).execute("delete from " + table + ";");
	}
	public synchronized static void refreshDB() {
		db = DBUtils.getEmbeddedDB("testdb", 2);
	}
	
	public static <T> List<T> queryList(String sql,Class<T> type) throws SQLException{
		DataSource dataSource=new EmbDataSource();
		QueryRunner run = new QueryRunner(dataSource);

		// Use the BeanListHandler implementation to convert all
		// ResultSet rows into a List of Person JavaBeans.
		ResultSetHandler<List<T>> h = new BeanListHandler<T>(type);
		
		// Execute the SQL statement and return the results in a List of
		// Person objects generated by the BeanListHandler.
			List<T> ret = run.query("SELECT * FROM TB_USER", h);
			return ret;
	}
	
	public final static String TB_USER_INSERT="TB_User_InsertValues.sql";

	public static void initTbUser() throws SQLException {
		List<User> initUsers = new ArrayList<User>();
		initUsers.add(new User("name1", "pass"));
		initUsers.add(new User("name2", "pass"));
		initUsers.add(new User("name1", "pass1"));
		initUsers.add(new User("name1", "pass2"));
		initUsers.add(new User("name2", "pass1"));
		initUsers.add(new User("name2", "pass2"));
		initUsers.add(new User("name", "pass1"));
		initUsers.add(new User("name", "pass2"));
		initUsers.add(new User("name3", "pass3"));
		initUsers.add(new User("name3", "pass4"));
		UserDao dao = new UserDao();
		dao.open();
		try {
			for (User user : initUsers) {
				dao.insert(user);
			}
		} finally {
			dao.close();
		}
	}
}
